﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
namespace WPFExcelReport
{
    public partial class VGSDAT
    {

        #region
        #endregion

        public void SetUpNewRow(ref DataRow dwReturn, DataRow dwInput = null)
        {

            switch (app.company)
            {
                case "YGR":
                case "TST":
                case "SXN":
                case "UBT":
                default:
                    #region=========
                    dwReturn["GSMANDANT"] = "00";
                    dwReturn["GSART"] = "                    ";
                    dwReturn["GSVAR"] = "   ";
                    dwReturn["GSAENDZL"] = "0";
                    dwReturn["GSAENDBEN"] = "        ";
                    dwReturn["GSAENDDAT"] = "0";
                    dwReturn["GSNEUBEN"] = "        ";
                    dwReturn["GSNEUDAT"] = "0";
                    dwReturn["GSMTART"] = "    ";
                    dwReturn["GSSTAKZ"] = "A";
                    dwReturn["GSFERTKZ"] = "E";
                    dwReturn["GSKUNDNR"] = "        ";
                    dwReturn["GSSTVAR"] = "J";
                    dwReturn["GSGSART"] = "   ";
                    dwReturn["GSPROF"] = " ";
                    dwReturn["GSMUSTERKZ"] = " ";
                    dwReturn["GSMURLOD"] = "00";
                    dwReturn["GSMUPROD"] = "00";
                    dwReturn["GSMUCTOD"] = " ";
                    dwReturn["GSMUDTRL"] = "0";
                    dwReturn["GSMUBIRL"] = "        ";
                    dwReturn["GSCOLORED"] = "J";
                    dwReturn["GSHANDLNR"] = "1                                                           ";
                    dwReturn["GSGARNBEZ"] = "                                        ";
                    dwReturn["GSAGARNNR"] = "               ";
                    dwReturn["GSTYP"] = "   ";
                    dwReturn["GSMAT"] = "CO    ";
                    dwReturn["GSNUMART"] = "NE  ";
                    dwReturn["GSGARNNR"] = "100000";
                    dwReturn["GSFACH"] = "1  ";
                    dwReturn["GSRECHNUM"] = "NE  ";
                    dwReturn["GSRECHNR"] = "100000";
                    dwReturn["GSREPRISE"] = "0";
                    dwReturn["GSIFEUCHTE"] = "0";
                    dwReturn["GSIFEUVTR"] = "0";
                    dwReturn["GSKAPIL"] = "0";
                    dwReturn["GSKAPILLG"] = "0";
                    dwReturn["GSTOME"] = "0";
                    dwReturn["GSDREH"] = " ";
                    dwReturn["GSTOMEVOR"] = "0";
                    dwReturn["GSDREHVOR"] = " ";
                    dwReturn["GSDREHWI"] = "0";
                    dwReturn["GSSURFACE"] = " ";
                    dwReturn["GSKZGM"] = " ";
                    dwReturn["GSCROSSEC"] = " ";
                    dwReturn["GSTEXTUR"] = " ";
                    dwReturn["GSEFFEKT"] = "   ";
                    dwReturn["GSEFFEKTLG"] = "0";
                    dwReturn["GSEFFEKTDM"] = "0";
                    dwReturn["GSTREATM1"] = "   ";
                    dwReturn["GSTREATM2"] = "   ";
                    dwReturn["GSWAX"] = " ";
                    dwReturn["GSUSE"] = "   ";
                    dwReturn["GSFIBPREP"] = " ";
                    dwReturn["GSSPINPRO"] = "   ";
                    dwReturn["GSPRODPRO"] = "   ";
                    dwReturn["GSPROGRP"] = "K  ";
                    dwReturn["GSARTGRP"] = "R  ";
                    dwReturn["GSTAX"] = "   ";
                    dwReturn["GSGRADE"] = "  ";
                    dwReturn["GSCLASS"] = "  ";
                    dwReturn["GSFORTE"] = "  ";
                    dwReturn["GSAUFMSL"] = "  ";
                    dwReturn["GSDURSP"] = "0";
                    dwReturn["GSKG"] = "0";
                    dwReturn["GSKGBIS"] = "0";
                    dwReturn["GSFDRES"] = "N";
                    dwReturn["GSHUELSE"] = "      ";
                    dwReturn["GSCOLVAR"] = "   ";
                    dwReturn["GSCOLPT"] = " ";
                    dwReturn["GSHERLD"] = "  ";
                    dwReturn["GSSAISON"] = "  ";
                    dwReturn["GSSTASCHL"] = "          ";
                    dwReturn["GSSTASL"] = "          ";
                    dwReturn["GSSTASL3"] = "          ";
                    dwReturn["GSBON"] = " ";
                    dwReturn["GSPMGRP"] = "    ";
                    dwReturn["GSSPINNA"] = "000";
                    dwReturn["GSTEXEF"] = "0";
                    dwReturn["GSMICRON"] = "0";
                    dwReturn["GSMICRONCV"] = "0";
                    dwReturn["GSFIBL"] = "0";
                    dwReturn["GSFIBLCV"] = "0";
                    dwReturn["GSFIBLMA"] = "0";
                    dwReturn["GSFIBDM"] = "0";
                    dwReturn["GSFIBL30"] = "0";
                    dwReturn["GSFIBL40"] = "0";
                    dwReturn["GSDRKFIBL"] = "0";
                    dwReturn["GSDRKFIBM"] = "0";
                    dwReturn["GSDRKFIBD"] = "0";
                    dwReturn["GSPRESSLEY"] = "0";
                    dwReturn["GSAVIV"] = "0";
                    dwReturn["GSSCIG"] = "0";
                    dwReturn["GSSCIGMA"] = "0";
                    dwReturn["GSSCIGAV"] = "0";
                    dwReturn["GSCNTEX"] = "0";
                    dwReturn["GSUV"] = "  ";
                    dwReturn["GSESACHB"] = "        ";
                    dwReturn["GSCADKEY"] = "                              ";
                    dwReturn["GSFAERBB"] = " ";
                    dwReturn["GSWEISGB"] = " ";
                    dwReturn["GSIONIZITAET"] = " ";
                    dwReturn["GSAFFINITY"] = "               ";
                    dwReturn["GSSUBST1"] = "  ";
                    dwReturn["GSSUBST2"] = "  ";
                    dwReturn["GSBLEACHED"] = " ";
                    dwReturn["GSCLORINEST"] = " ";
                    dwReturn["GSFIXTEMP"] = "0";
                    dwReturn["GSFBTXT1"] = "    ";
                    dwReturn["GSFBTXT2"] = "    ";
                    dwReturn["GSFBTXT3"] = "    ";
                    dwReturn["GSFBTXT4"] = "    ";
                    dwReturn["GSFBTXT5"] = "    ";
                    dwReturn["GSSUBSTGR"] = "   ";
                    dwReturn["GSFALTEMP"] = "   ";
                    dwReturn["GSKAKZ"] = "J";
                    dwReturn["GSKARTZU"] = "J";
                    dwReturn["GSSPULBST"] = " ";
                    dwReturn["GSPFVARN"] = " ";
                    dwReturn["GSPRUEFANW"] = " ";
                    dwReturn["GSPRUEF"] = "    ";
                    dwReturn["GSFKART"] = "          ";
                    dwReturn["GSFRBST"] = "  ";
                    dwReturn["GSDISPOKZ"] = " ";
                    dwReturn["GSPLGRP"] = "  ";
                    dwReturn["GSPLANART"] = "                    ";
                    dwReturn["GSAUFKZ"] = " ";
                    dwReturn["GSPRODH"] = "                  ";
                    dwReturn["GSMATKL"] = "         ";
                    dwReturn["GSBKLAS"] = "    ";
                    dwReturn["GSKTO1"] = "      ";
                    dwReturn["GSMATKTO"] = "      ";
                    dwReturn["GSMGKMM"] = "        ";
                    dwReturn["GSRAVGRP"] = "    ";
                    dwReturn["GSKAZGRP"] = " ";
                    dwReturn["GSPRGRPN1"] = " ";
                    dwReturn["GSPRGRPN2"] = " ";
                    dwReturn["GSPRGRPN3"] = " ";
                    dwReturn["GSKAART"] = "                    ";
                    dwReturn["GSKAVAR"] = "   ";
                    dwReturn["GSKASTD"] = " ";
                    dwReturn["GSVRPR"] = "0";
                    dwReturn["GSBWPR1"] = "0";
                    dwReturn["GSBWPR2"] = "0";
                    dwReturn["GSBWPR3"] = "0";
                    dwReturn["GSBWPLAN"] = " ";
                    dwReturn["GSZTWAHL"] = "0";
                    dwReturn["GSVERLUST"] = "0";
                    dwReturn["GSKALKMG"] = "0";
                    dwReturn["GSKSACHB"] = "        ";
                    dwReturn["GSEKGRP"] = "   ";
                    dwReturn["GSBSTME"] = "   ";
                    dwReturn["GSVABME"] = " ";
                    dwReturn["GSEKWSL"] = "    ";
                    dwReturn["GSFILLER"] = "                                                                                      ";

                    #endregion
                    break;

            }
            INTEXDBC.MapData(true, ref dwReturn, dwInput);
        }

        public DataRow Convert2GSDAT(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryGSDAT(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
                    dwReturn["GSMANDANT"] = "00";
                    dwReturn["GSART"] = "                    ";
                    dwReturn["GSVAR"] = "   ";
                    dwReturn["GSAENDZL"] = "0";
                    dwReturn["GSAENDBEN"] = "        ";
                    dwReturn["GSAENDDAT"] = "0";
                    dwReturn["GSNEUBEN"] = "        ";
                    dwReturn["GSNEUDAT"] = "0";
                    dwReturn["GSMTART"] = "    ";
                    dwReturn["GSSTAKZ"] = "A";
                    dwReturn["GSFERTKZ"] = "E";
                    dwReturn["GSKUNDNR"] = "        ";
                    dwReturn["GSSTVAR"] = "J";
                    dwReturn["GSGSART"] = "   ";
                    dwReturn["GSPROF"] = " ";
                    dwReturn["GSMUSTERKZ"] = " ";
                    dwReturn["GSMURLOD"] = "00";
                    dwReturn["GSMUPROD"] = "00";
                    dwReturn["GSMUCTOD"] = " ";
                    dwReturn["GSMUDTRL"] = "0";
                    dwReturn["GSMUBIRL"] = "        ";
                    dwReturn["GSCOLORED"] = "J";
                    dwReturn["GSHANDLNR"] = "1                                                           ";
                    dwReturn["GSGARNBEZ"] = "                                        ";
                    dwReturn["GSAGARNNR"] = "               ";
                    dwReturn["GSTYP"] = "   ";
                    dwReturn["GSMAT"] = "CO    ";
                    dwReturn["GSNUMART"] = "NE  ";
                    dwReturn["GSGARNNR"] = "100000";
                    dwReturn["GSFACH"] = "1  ";
                    dwReturn["GSRECHNUM"] = "NE  ";
                    dwReturn["GSRECHNR"] = "100000";
                    dwReturn["GSREPRISE"] = "0";
                    dwReturn["GSIFEUCHTE"] = "0";
                    dwReturn["GSIFEUVTR"] = "0";
                    dwReturn["GSKAPIL"] = "0";
                    dwReturn["GSKAPILLG"] = "0";
                    dwReturn["GSTOME"] = "0";
                    dwReturn["GSDREH"] = " ";
                    dwReturn["GSTOMEVOR"] = "0";
                    dwReturn["GSDREHVOR"] = " ";
                    dwReturn["GSDREHWI"] = "0";
                    dwReturn["GSSURFACE"] = " ";
                    dwReturn["GSKZGM"] = " ";
                    dwReturn["GSCROSSEC"] = " ";
                    dwReturn["GSTEXTUR"] = " ";
                    dwReturn["GSEFFEKT"] = "   ";
                    dwReturn["GSEFFEKTLG"] = "0";
                    dwReturn["GSEFFEKTDM"] = "0";
                    dwReturn["GSTREATM1"] = "   ";
                    dwReturn["GSTREATM2"] = "   ";
                    dwReturn["GSWAX"] = " ";
                    dwReturn["GSUSE"] = "   ";
                    dwReturn["GSFIBPREP"] = " ";
                    dwReturn["GSSPINPRO"] = "   ";
                    dwReturn["GSPRODPRO"] = "   ";
                    dwReturn["GSPROGRP"] = "K  ";
                    dwReturn["GSARTGRP"] = "R  ";
                    dwReturn["GSTAX"] = "   ";
                    dwReturn["GSGRADE"] = "  ";
                    dwReturn["GSCLASS"] = "  ";
                    dwReturn["GSFORTE"] = "  ";
                    dwReturn["GSAUFMSL"] = "  ";
                    dwReturn["GSDURSP"] = "0";
                    dwReturn["GSKG"] = "0";
                    dwReturn["GSKGBIS"] = "0";
                    dwReturn["GSFDRES"] = "N";
                    dwReturn["GSHUELSE"] = "      ";
                    dwReturn["GSCOLVAR"] = "   ";
                    dwReturn["GSCOLPT"] = " ";
                    dwReturn["GSHERLD"] = "  ";
                    dwReturn["GSSAISON"] = "  ";
                    dwReturn["GSSTASCHL"] = "          ";
                    dwReturn["GSSTASL"] = "          ";
                    dwReturn["GSSTASL3"] = "          ";
                    dwReturn["GSBON"] = " ";
                    dwReturn["GSPMGRP"] = "    ";
                    dwReturn["GSSPINNA"] = "000";
                    dwReturn["GSTEXEF"] = "0";
                    dwReturn["GSMICRON"] = "0";
                    dwReturn["GSMICRONCV"] = "0";
                    dwReturn["GSFIBL"] = "0";
                    dwReturn["GSFIBLCV"] = "0";
                    dwReturn["GSFIBLMA"] = "0";
                    dwReturn["GSFIBDM"] = "0";
                    dwReturn["GSFIBL30"] = "0";
                    dwReturn["GSFIBL40"] = "0";
                    dwReturn["GSDRKFIBL"] = "0";
                    dwReturn["GSDRKFIBM"] = "0";
                    dwReturn["GSDRKFIBD"] = "0";
                    dwReturn["GSPRESSLEY"] = "0";
                    dwReturn["GSAVIV"] = "0";
                    dwReturn["GSSCIG"] = "0";
                    dwReturn["GSSCIGMA"] = "0";
                    dwReturn["GSSCIGAV"] = "0";
                    dwReturn["GSCNTEX"] = "0";
                    dwReturn["GSUV"] = "  ";
                    dwReturn["GSESACHB"] = "        ";
                    dwReturn["GSCADKEY"] = "                              ";
                    dwReturn["GSFAERBB"] = " ";
                    dwReturn["GSWEISGB"] = " ";
                    dwReturn["GSIONIZITAET"] = " ";
                    dwReturn["GSAFFINITY"] = "               ";
                    dwReturn["GSSUBST1"] = "  ";
                    dwReturn["GSSUBST2"] = "  ";
                    dwReturn["GSBLEACHED"] = " ";
                    dwReturn["GSCLORINEST"] = " ";
                    dwReturn["GSFIXTEMP"] = "0";
                    dwReturn["GSFBTXT1"] = "    ";
                    dwReturn["GSFBTXT2"] = "    ";
                    dwReturn["GSFBTXT3"] = "    ";
                    dwReturn["GSFBTXT4"] = "    ";
                    dwReturn["GSFBTXT5"] = "    ";
                    dwReturn["GSSUBSTGR"] = "   ";
                    dwReturn["GSFALTEMP"] = "   ";
                    dwReturn["GSKAKZ"] = "J";
                    dwReturn["GSKARTZU"] = "J";
                    dwReturn["GSSPULBST"] = " ";
                    dwReturn["GSPFVARN"] = " ";
                    dwReturn["GSPRUEFANW"] = " ";
                    dwReturn["GSPRUEF"] = "    ";
                    dwReturn["GSFKART"] = "          ";
                    dwReturn["GSFRBST"] = "  ";
                    dwReturn["GSDISPOKZ"] = " ";
                    dwReturn["GSPLGRP"] = "  ";
                    dwReturn["GSPLANART"] = "                    ";
                    dwReturn["GSAUFKZ"] = " ";
                    dwReturn["GSPRODH"] = "                  ";
                    dwReturn["GSMATKL"] = "         ";
                    dwReturn["GSBKLAS"] = "    ";
                    dwReturn["GSKTO1"] = "      ";
                    dwReturn["GSMATKTO"] = "      ";
                    dwReturn["GSMGKMM"] = "        ";
                    dwReturn["GSRAVGRP"] = "    ";
                    dwReturn["GSKAZGRP"] = " ";
                    dwReturn["GSPRGRPN1"] = " ";
                    dwReturn["GSPRGRPN2"] = " ";
                    dwReturn["GSPRGRPN3"] = " ";
                    dwReturn["GSKAART"] = "                    ";
                    dwReturn["GSKAVAR"] = "   ";
                    dwReturn["GSKASTD"] = " ";
                    dwReturn["GSVRPR"] = "0";
                    dwReturn["GSBWPR1"] = "0";
                    dwReturn["GSBWPR2"] = "0";
                    dwReturn["GSBWPR3"] = "0";
                    dwReturn["GSBWPLAN"] = " ";
                    dwReturn["GSZTWAHL"] = "0";
                    dwReturn["GSVERLUST"] = "0";
                    dwReturn["GSKALKMG"] = "0";
                    dwReturn["GSKSACHB"] = "        ";
                    dwReturn["GSEKGRP"] = "   ";
                    dwReturn["GSBSTME"] = "   ";
                    dwReturn["GSVABME"] = " ";
                    dwReturn["GSEKWSL"] = "    ";
                    dwReturn["GSFILLER"] = "                                                                                      ";

                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }

        public DataSet QueryGSDAT(DataRow dwData, int option = 1)
        {

            switch (option)
            {
                case 0:
                    SResult = QGS00(dwData);
                    break;
                case 1:
                    SResult = QGS01(dwData);
                    break;
                case 2:
                    SResult = QGS02(dwData);
                    break;
                case 4:
                    SResult = QGS04(dwData);
                    break;
                case 5:
                    SResult = QGS05(dwData);
                    break;
                case 6:
                    SResult = QGS06(dwData);
                    break;
                case 7:
                    SResult = QGS07(dwData);
                    break;
                case -1:
                    SResult = QGS_1(dwData);
                    break;
                case -2:
                    SResult = QGS_2(dwData);
                    break;
                default:
                    break;
            }
            return SResult;
        }
        public DataSet QGS00(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT * FROM GSDAT WHERE  ROWNUM<2 ";

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;


            return SResult;
        }
        public DataSet QGS01(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GSDAT WHERE  "
            + "  GSMANDANT=:GSMANDANT: AND GSART=:GSART: AND GSVAR=:GSVAR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGS02(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT DISTINCT GS.* FROM EKPOS GA LEFT JOIN GSDAT GS ON EKPMANDANT=GSMANDANT   AND utl_raw.cast_to_varchar2(EKPART)=GSART"
                + "  AND utl_raw.cast_to_varchar2(EKPVAR)=GSVAR"
                + "  WHERE EKPSA='PO' AND EKPARTGR=1 AND EKPMANDANT=:EKPMANDANT: AND  EKPAUFKRS=:EKPAUFKRS: AND EKPAUFLFD=:EKPAUFLFD: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGS04(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();

            or1.CommandText = "  SELECT DISTINCT GS.* FROM GAAUF GA RIGHT JOIN GSDAT GS ON GAMANDANT=GSMANDANT"
                + " AND GAART=GSART "
                + "  WHERE GAMANDANT=:GAMANDANT: AND  GAAUFKRS=:GAAUFKRS: AND GAAUFLFD=:GAAUFLFD: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;


            return SResult;
        }
        public DataSet QGS05(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT DISTINCT GSCMANDANT AS GSMANDANT,GSCART AS GSART,GSCVAR AS GSVAR"
            + " FROM GSCOLOR WHERE GSCART NOT IN (SELECT GSART FROM GSDAT WHERE GSMANDANT=:GSMANDANT: ) ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGS06(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT GAAUFKRS,GAAUFLFD,GAAUFPOS,M.* FROM VKPOS JOIN GAAUF ON VKPSA='  ' AND VKPMANDANT=GAMANDANT AND VKPAUFKRS=GAAUFKRS AND VKPAUFLFD=GAAUFLFD AND VKPAUFPOS=GAAUFPOS AND GASA=610"
            + " JOIN GSDAT M ON  GSART=GAART AND GSVAR=GAVAR "
                + "WHERE VKPMANDANT=:VKPMANDANT: AND VKPAUFKRS=:VKPAUFKRS: AND VKPAUFLFD=:VKPAUFLFD: ";
            or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGS07(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT GAAUFKRS,GAAUFLFD,GAAUFPOS,M.* FROM VKPOS JOIN GAAUF ON VKPSA='  ' AND VKPMANDANT=GAMANDANT AND VKPAUFKRS=GAAUFKRS AND VKPAUFLFD=GAAUFLFD AND VKPAUFPOS=GAAUFPOS AND GASA=610"
            + " JOIN GSDAT M ON  GSART=GAART AND GSVAR=GAVAR "
                + "WHERE VKPMANDANT=:VKPMANDANT: AND VKPAUFKRS=:VKPAUFKRS: AND VKPAUFLFD=:VKPAUFLFD: AND VKPAUFPOS=:VKPAUFPOS: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;

            return SResult;
        }
        public DataSet QGS08(DataRow dwData )
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT GSBWPR1/1000 AS GSKGPR,P.* FROM GSDAT P  "
            + " WHERE GSMANDANT=:GSMANDANT:"
            + dwData["GSART"].ToString()
            + dwData["GSVAR"].ToString()
            + dwData["GSHANDLNR"].ToString()
            + " ORDER BY GSART,GSVAR";

            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGS09(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT DISTINCT GSART,GSVAR,GSART AS KEY,GSHANDLNR,GSHANDLNR AS DES FROM GSDAT "
                + " WHERE GSMANDANT=:GSMANDANT: AND GSART LIKE '" + dwData["GSART"].ToString().Trim() + "%'"
                + " ORDER BY 1,2";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName; 
            return SResult;
        }
        public DataSet QGS_1(DataRow dwData )
        {

            or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT DISTINCT GSART,GSVAR,GSART||GSVAR AS KEY,GSHANDLNR,GSHANDLNR AS DES FROM GSDAT "
                + " WHERE GSMANDANT=:GSMANDANT: AND GSART LIKE '" + dwData["GSART"].ToString().Trim() + "%'"
                + " ORDER BY 1,2";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;


            return SResult;
        }
        public DataSet QGS_2(DataRow dwData )
        {

            or1 = OracleDB.CreateCommand(); 
            or1.CommandText = "SELECT DISTINCT  GSGARNNR,GSFACH,GSGARNNR*0.0001||'/'||GSFACH AS YNE FROM GSDAT "; 
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName; 
            return SResult;
        }

        public void MapGSDAT(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveGSDAT(DataRow dwData, string ITXDBVERSION = "50.24", bool update = true)
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryGSDAT(dwData, 1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapGSDAT(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertGSDAT(dwLocal, ITXDBVERSION);
            }
            if (update)
            {
                UpdateGSDAT(dwLocal);
            }
        }
        public void InsertGSDAT(DataRow dwData, string ITXDBVERSION = "50.24")
        {
           
            dwData["GSNEUBEN"] = app.User;
            dwData["GSNEUDAT"] = date;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                case "TST":
                case "SXN":
                case "UBT":
                default:
                    or1.CommandText = "INSERT INTO GSDAT ( GSMANDANT,GSART,GSVAR,GSAENDZL,GSAENDBEN,GSAENDDAT,GSNEUBEN,GSNEUDAT,GSMTART,GSSTAKZ,GSFERTKZ,GSKUNDNR,GSSTVAR,GSGSART,GSPROF,GSMUSTERKZ,GSMURLOD,GSMUPROD,GSMUCTOD,GSMUDTRL,GSMUBIRL,GSCOLORED,GSHANDLNR,GSGARNBEZ,GSAGARNNR,GSTYP,GSMAT,GSNUMART,GSGARNNR,GSFACH,GSRECHNUM,GSRECHNR,GSREPRISE,GSIFEUCHTE,GSIFEUVTR,GSKAPIL,GSKAPILLG,GSTOME,GSDREH,GSTOMEVOR,GSDREHVOR,GSDREHWI,GSSURFACE,GSKZGM,GSCROSSEC,GSTEXTUR,GSEFFEKT,GSEFFEKTLG,GSEFFEKTDM,GSTREATM1,GSTREATM2,GSWAX,GSUSE,GSFIBPREP,GSSPINPRO,GSPRODPRO,GSPROGRP,GSARTGRP,GSTAX,GSGRADE,GSCLASS,GSFORTE,GSAUFMSL,GSDURSP,GSKG,GSKGBIS,GSFDRES,GSHUELSE,GSCOLVAR,GSCOLPT,GSHERLD,GSSAISON,GSSTASCHL,GSSTASL,GSSTASL3,GSBON,GSPMGRP,GSSPINNA,GSTEXEF,GSMICRON,GSMICRONCV,GSFIBL,GSFIBLCV,GSFIBLMA,GSFIBDM,GSFIBL30,GSFIBL40,GSDRKFIBL,GSDRKFIBM,GSDRKFIBD,GSPRESSLEY,GSAVIV,GSSCIG,GSSCIGMA,GSSCIGAV,GSCNTEX,GSUV,GSESACHB,GSCADKEY,GSFAERBB,GSWEISGB,GSIONIZITAET,GSAFFINITY,GSSUBST1,GSSUBST2,GSBLEACHED,GSCLORINEST,GSFIXTEMP,GSFBTXT1,GSFBTXT2,GSFBTXT3,GSFBTXT4,GSFBTXT5,GSSUBSTGR,GSFALTEMP,GSKAKZ,GSKARTZU,GSSPULBST,GSPFVARN,GSPRUEFANW,GSPRUEF,GSFKART,GSFRBST,GSDISPOKZ,GSPLGRP,GSPLANART,GSAUFKZ,GSPRODH,GSMATKL,GSBKLAS,GSKTO1,GSMATKTO,GSMGKMM,GSRAVGRP,GSKAZGRP,GSPRGRPN1,GSPRGRPN2,GSPRGRPN3,GSKAART,GSKAVAR,GSKASTD,GSVRPR,GSBWPR1,GSBWPR2,GSBWPR3,GSBWPLAN,GSZTWAHL,GSVERLUST,GSKALKMG,GSKSACHB,GSEKGRP,GSBSTME,GSVABME,GSEKWSL,GSFILLER)"
                    + " VALUES (:GSMANDANT:,:GSART:,:GSVAR:,:GSAENDZL:,:GSAENDBEN:,:GSAENDDAT:,:GSNEUBEN:,:GSNEUDAT:,:GSMTART:,:GSSTAKZ:,:GSFERTKZ:,:GSKUNDNR:,:GSSTVAR:,:GSGSART:,:GSPROF:,:GSMUSTERKZ:,:GSMURLOD:,:GSMUPROD:,:GSMUCTOD:,:GSMUDTRL:,:GSMUBIRL:,:GSCOLORED:,:GSHANDLNR:,:GSGARNBEZ:,:GSAGARNNR:,:GSTYP:,:GSMAT:,:GSNUMART:,:GSGARNNR:,:GSFACH:,:GSRECHNUM:,:GSRECHNR:,:GSREPRISE:,:GSIFEUCHTE:,:GSIFEUVTR:,:GSKAPIL:,:GSKAPILLG:,:GSTOME:,:GSDREH:,:GSTOMEVOR:,:GSDREHVOR:,:GSDREHWI:,:GSSURFACE:,:GSKZGM:,:GSCROSSEC:,:GSTEXTUR:,:GSEFFEKT:,:GSEFFEKTLG:,:GSEFFEKTDM:,:GSTREATM1:,:GSTREATM2:,:GSWAX:,:GSUSE:,:GSFIBPREP:,:GSSPINPRO:,:GSPRODPRO:,:GSPROGRP:,:GSARTGRP:,:GSTAX:,:GSGRADE:,:GSCLASS:,:GSFORTE:,:GSAUFMSL:,:GSDURSP:,:GSKG:,:GSKGBIS:,:GSFDRES:,:GSHUELSE:,:GSCOLVAR:,:GSCOLPT:,:GSHERLD:,:GSSAISON:,:GSSTASCHL:,:GSSTASL:,:GSSTASL3:,:GSBON:,:GSPMGRP:,:GSSPINNA:,:GSTEXEF:,:GSMICRON:,:GSMICRONCV:,:GSFIBL:,:GSFIBLCV:,:GSFIBLMA:,:GSFIBDM:,:GSFIBL30:,:GSFIBL40:,:GSDRKFIBL:,:GSDRKFIBM:,:GSDRKFIBD:,:GSPRESSLEY:,:GSAVIV:,:GSSCIG:,:GSSCIGMA:,:GSSCIGAV:,:GSCNTEX:,:GSUV:,:GSESACHB:,:GSCADKEY:,:GSFAERBB:,:GSWEISGB:,:GSIONIZITAET:,:GSAFFINITY:,:GSSUBST1:,:GSSUBST2:,:GSBLEACHED:,:GSCLORINEST:,:GSFIXTEMP:,:GSFBTXT1:,:GSFBTXT2:,:GSFBTXT3:,:GSFBTXT4:,:GSFBTXT5:,:GSSUBSTGR:,:GSFALTEMP:,:GSKAKZ:,:GSKARTZU:,:GSSPULBST:,:GSPFVARN:,:GSPRUEFANW:,:GSPRUEF:,:GSFKART:,:GSFRBST:,:GSDISPOKZ:,:GSPLGRP:,:GSPLANART:,:GSAUFKZ:,:GSPRODH:,:GSMATKL:,:GSBKLAS:,:GSKTO1:,:GSMATKTO:,:GSMGKMM:,:GSRAVGRP:,:GSKAZGRP:,:GSPRGRPN1:,:GSPRGRPN2:,:GSPRGRPN3:,:GSKAART:,:GSKAVAR:,:GSKASTD:,:GSVRPR:,:GSBWPR1:,:GSBWPR2:,:GSBWPR3:,:GSBWPLAN:,:GSZTWAHL:,:GSVERLUST:,:GSKALKMG:,:GSKSACHB:,:GSEKGRP:,:GSBSTME:,:GSVABME:,:GSEKWSL:,:GSFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }

            Connect2Proxy(or1);
        }
        public override string BATCHINSERT(DataRow dwData)
        {
            dwData["GSNEUBEN"] = app.User;
            dwData["GSNEUDAT"] = date;
            string value = "";
            switch (app.company)
            {
                default:
                case "SXN":
                case "YGR":
                case "TST":
                    value = "INTO GSDAT  "
                    + " VALUES (:GSMANDANT:,:GSART:,:GSVAR:,:GSAENDZL:,:GSAENDBEN:,:GSAENDDAT:,:GSNEUBEN:,:GSNEUDAT:,:GSMTART:,:GSSTAKZ:,:GSFERTKZ:,:GSKUNDNR:,:GSSTVAR:,:GSGSART:,:GSPROF:,:GSMUSTERKZ:,:GSMURLOD:,:GSMUPROD:,:GSMUCTOD:,:GSMUDTRL:,:GSMUBIRL:,:GSCOLORED:,:GSHANDLNR:,:GSGARNBEZ:,:GSAGARNNR:,:GSTYP:,:GSMAT:,:GSNUMART:,:GSGARNNR:,:GSFACH:,:GSRECHNUM:,:GSRECHNR:,:GSREPRISE:,:GSIFEUCHTE:,:GSIFEUVTR:,:GSKAPIL:,:GSKAPILLG:,:GSTOME:,:GSDREH:,:GSTOMEVOR:,:GSDREHVOR:,:GSDREHWI:,:GSSURFACE:,:GSKZGM:,:GSCROSSEC:,:GSTEXTUR:,:GSEFFEKT:,:GSEFFEKTLG:,:GSEFFEKTDM:,:GSTREATM1:,:GSTREATM2:,:GSWAX:,:GSUSE:,:GSFIBPREP:,:GSSPINPRO:,:GSPRODPRO:,:GSPROGRP:,:GSARTGRP:,:GSTAX:,:GSGRADE:,:GSCLASS:,:GSFORTE:,:GSAUFMSL:,:GSDURSP:,:GSKG:,:GSKGBIS:,:GSFDRES:,:GSHUELSE:,:GSCOLVAR:,:GSCOLPT:,:GSHERLD:,:GSSAISON:,:GSSTASCHL:,:GSSTASL:,:GSSTASL3:,:GSBON:,:GSPMGRP:,:GSSPINNA:,:GSTEXEF:,:GSMICRON:,:GSMICRONCV:,:GSFIBL:,:GSFIBLCV:,:GSFIBLMA:,:GSFIBDM:,:GSFIBL30:,:GSFIBL40:,:GSDRKFIBL:,:GSDRKFIBM:,:GSDRKFIBD:,:GSPRESSLEY:,:GSAVIV:,:GSSCIG:,:GSSCIGMA:,:GSSCIGAV:,:GSCNTEX:,:GSUV:,:GSESACHB:,:GSCADKEY:,:GSFAERBB:,:GSWEISGB:,:GSIONIZITAET:,:GSAFFINITY:,:GSSUBST1:,:GSSUBST2:,:GSBLEACHED:,:GSCLORINEST:,:GSFIXTEMP:,:GSFBTXT1:,:GSFBTXT2:,:GSFBTXT3:,:GSFBTXT4:,:GSFBTXT5:,:GSSUBSTGR:,:GSFALTEMP:,:GSKAKZ:,:GSKARTZU:,:GSSPULBST:,:GSPFVARN:,:GSPRUEFANW:,:GSPRUEF:,:GSFKART:,:GSFRBST:,:GSDISPOKZ:,:GSPLGRP:,:GSPLANART:,:GSAUFKZ:,:GSPRODH:,:GSMATKL:,:GSBKLAS:,:GSKTO1:,:GSMATKTO:,:GSMGKMM:,:GSRAVGRP:,:GSKAZGRP:,:GSPRGRPN1:,:GSPRGRPN2:,:GSPRGRPN3:,:GSKAART:,:GSKAVAR:,:GSKASTD:,:GSVRPR:,:GSBWPR1:,:GSBWPR2:,:GSBWPR3:,:GSBWPLAN:,:GSZTWAHL:,:GSVERLUST:,:GSKALKMG:,:GSKSACHB:,:GSEKGRP:,:GSBSTME:,:GSVABME:,:GSEKWSL:,:GSFILLER:)";
                    value = INTEXDBC.InjectData(value, dwData);
                    break;
            }
            return value;
        }
        public void DeleteGSDAT(DataRow dwData, int option = 1)
        {
            switch (option)
            {
                case 1:
                    DGS01(dwData);
                    break;
                default:
                    break;
            }

        }
        public void DGS01(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand(); 
            or1.CommandText = "DELETE FROM GSDAT  WHERE  GSMANDANT=:GSMANDANT: AND GSART=:GSART: AND GSVAR =:GSVAR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
            Connect2Proxy(or1);
        }
        public void DGS00(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM GSDAT  WHERE  GSMANDANT=:GSMANDANT:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UpdateGSDAT(DataRow dwData, int option = 1)
        {
            if (option == 0)
            {
                switch (app.company)
                {
                    case "YGR":
                    case "TST":
                    case "SXN":
                    case "UBT":
                    default:
                        option = 1;
                        break;
                }
            }
            dwData["GSAENDBEN"] = app.User;
            dwData["GSAENDDAT"] = date;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE GSDAT SET GSMANDANT=:GSMANDANT:,GSART=:GSART:,GSVAR=:GSVAR:,GSAENDZL=GSAENDZL+GSAENDZL,GSAENDBEN=:GSAENDBEN:,GSAENDDAT=:GSAENDDAT:,GSNEUBEN=:GSNEUBEN:,GSNEUDAT=:GSNEUDAT:,GSMTART=:GSMTART:,GSSTAKZ=:GSSTAKZ:,GSFERTKZ=:GSFERTKZ:,GSKUNDNR=:GSKUNDNR:,GSSTVAR=:GSSTVAR:,GSGSART=:GSGSART:,GSPROF=:GSPROF:,GSMUSTERKZ=:GSMUSTERKZ:,GSMURLOD=:GSMURLOD:,GSMUPROD=:GSMUPROD:,GSMUCTOD=:GSMUCTOD:,GSMUDTRL=:GSMUDTRL:,GSMUBIRL=:GSMUBIRL:,GSCOLORED=:GSCOLORED:,GSHANDLNR=:GSHANDLNR:,GSGARNBEZ=:GSGARNBEZ:,GSAGARNNR=:GSAGARNNR:,GSTYP=:GSTYP:,GSMAT=:GSMAT:,GSNUMART=:GSNUMART:,GSGARNNR=:GSGARNNR:,GSFACH=:GSFACH:,GSRECHNUM=:GSRECHNUM:,GSRECHNR=:GSRECHNR:,GSREPRISE=:GSREPRISE:,GSIFEUCHTE=:GSIFEUCHTE:,GSIFEUVTR=:GSIFEUVTR:,GSKAPIL=:GSKAPIL:,GSKAPILLG=:GSKAPILLG:,GSTOME=:GSTOME:,GSDREH=:GSDREH:,GSTOMEVOR=:GSTOMEVOR:,GSDREHVOR=:GSDREHVOR:,GSDREHWI=:GSDREHWI:,GSSURFACE=:GSSURFACE:,GSKZGM=:GSKZGM:,GSCROSSEC=:GSCROSSEC:,GSTEXTUR=:GSTEXTUR:,GSEFFEKT=:GSEFFEKT:,GSEFFEKTLG=:GSEFFEKTLG:,GSEFFEKTDM=:GSEFFEKTDM:,GSTREATM1=:GSTREATM1:,GSTREATM2=:GSTREATM2:,GSWAX=:GSWAX:,GSUSE=:GSUSE:,GSFIBPREP=:GSFIBPREP:,GSSPINPRO=:GSSPINPRO:,GSPRODPRO=:GSPRODPRO:,GSPROGRP=:GSPROGRP:,GSARTGRP=:GSARTGRP:,GSTAX=:GSTAX:,GSGRADE=:GSGRADE:,GSCLASS=:GSCLASS:,GSFORTE=:GSFORTE:,GSAUFMSL=:GSAUFMSL:,GSDURSP=:GSDURSP:,GSKG=:GSKG:,GSKGBIS=:GSKGBIS:,GSFDRES=:GSFDRES:,GSHUELSE=:GSHUELSE:,GSCOLVAR=:GSCOLVAR:,GSCOLPT=:GSCOLPT:,GSHERLD=:GSHERLD:,GSSAISON=:GSSAISON:,GSSTASCHL=:GSSTASCHL:,GSSTASL=:GSSTASL:,GSSTASL3=:GSSTASL3:,GSBON=:GSBON:,GSPMGRP=:GSPMGRP:,GSSPINNA=:GSSPINNA:,GSTEXEF=:GSTEXEF:,GSMICRON=:GSMICRON:,GSMICRONCV=:GSMICRONCV:,GSFIBL=:GSFIBL:,GSFIBLCV=:GSFIBLCV:,GSFIBLMA=:GSFIBLMA:,GSFIBDM=:GSFIBDM:,GSFIBL30=:GSFIBL30:,GSFIBL40=:GSFIBL40:,GSDRKFIBL=:GSDRKFIBL:,GSDRKFIBM=:GSDRKFIBM:,GSDRKFIBD=:GSDRKFIBD:,GSPRESSLEY=:GSPRESSLEY:,GSAVIV=:GSAVIV:,GSSCIG=:GSSCIG:,GSSCIGMA=:GSSCIGMA:,GSSCIGAV=:GSSCIGAV:,GSCNTEX=:GSCNTEX:,GSUV=:GSUV:,GSESACHB=:GSESACHB:,GSCADKEY=:GSCADKEY:,GSFAERBB=:GSFAERBB:,GSWEISGB=:GSWEISGB:,GSIONIZITAET=:GSIONIZITAET:,GSAFFINITY=:GSAFFINITY:,GSSUBST1=:GSSUBST1:,GSSUBST2=:GSSUBST2:,GSBLEACHED=:GSBLEACHED:,GSCLORINEST=:GSCLORINEST:,GSFIXTEMP=:GSFIXTEMP:,GSFBTXT1=:GSFBTXT1:,GSFBTXT2=:GSFBTXT2:,GSFBTXT3=:GSFBTXT3:,GSFBTXT4=:GSFBTXT4:,GSFBTXT5=:GSFBTXT5:,GSSUBSTGR=:GSSUBSTGR:,GSFALTEMP=:GSFALTEMP:,GSKAKZ=:GSKAKZ:,GSKARTZU=:GSKARTZU:,GSSPULBST=:GSSPULBST:,GSPFVARN=:GSPFVARN:,GSPRUEFANW=:GSPRUEFANW:,GSPRUEF=:GSPRUEF:,GSFKART=:GSFKART:,GSFRBST=:GSFRBST:,GSDISPOKZ=:GSDISPOKZ:,GSPLGRP=:GSPLGRP:,GSPLANART=:GSPLANART:,GSAUFKZ=:GSAUFKZ:,GSPRODH=:GSPRODH:,GSMATKL=:GSMATKL:,GSBKLAS=:GSBKLAS:,GSKTO1=:GSKTO1:,GSMATKTO=:GSMATKTO:,GSMGKMM=:GSMGKMM:,GSRAVGRP=:GSRAVGRP:,GSKAZGRP=:GSKAZGRP:,GSPRGRPN1=:GSPRGRPN1:,GSPRGRPN2=:GSPRGRPN2:,GSPRGRPN3=:GSPRGRPN3:,GSKAART=:GSKAART:,GSKAVAR=:GSKAVAR:,GSKASTD=:GSKASTD:,GSVRPR=:GSVRPR:,GSBWPR1=:GSBWPR1:,GSBWPR2=:GSBWPR2:,GSBWPR3=:GSBWPR3:,GSBWPLAN=:GSBWPLAN:,GSZTWAHL=:GSZTWAHL:,GSVERLUST=:GSVERLUST:,GSKALKMG=:GSKALKMG:,GSKSACHB=:GSKSACHB:,GSEKGRP=:GSEKGRP:,GSBSTME=:GSBSTME:,GSVABME=:GSVABME:,GSEKWSL=:GSEKWSL:,GSFILLER=:GSFILLER:"
                        + " WHERE  GSMANDANT=:GSMANDANT: AND GSART=:GSART: AND GSVAR =:GSVAR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
